﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;
namespace DAO
{
    public class InsertKQDAO
    {
        public static Boolean InsertKetQua(TTKetQuaDTO tt)
        {
            bool ok = false;

            string sp = "usp_GPB_InsertKQ ";
            sp += String.Format("'{0}',{1},N{2},N{3},N{4}"
                                + ",'{5}',{6},'{7}','{8}'"
                                + ",{9},{10},'{11}','{12}'"
                                + ",{13},{14},'{15}','{16}','{17}',{18}"
                                + ",{19},'{20}'",
                 tt.SoBN, tt.Lan,Uit.it_SQL.ToSQLString(tt.KQDaiThe1), Uit.it_SQL.ToSQLString(tt.KQViThe1)
                 , Uit.it_SQL.ToSQLString(tt.KetLuan1) , tt.UrlHinh1, tt.IDBacSiTH, tt.CDate,tt.SoTBan
                 ,tt.IDKTV,tt.LoaiKQ,tt.Host,tt.UserName,tt.SoLam,tt.IDNguoiCat,tt.NgayCat
                 ,tt.NgayLenLam,tt.IDPPNhuom, tt.SoLamHM
                 , tt.SoMauCat, tt.GhiChu);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);

            return ok;
        }

        public static Boolean InsertKetQuaPAP(TTKetQuaPAPDTO tt)
        {
            bool ok = false;

            string sp = "usp_GPB_InsertKQPAP ";
            sp += String.Format("'{0}','{1}','{2}',N'{3}',{4}"
                                + ",{5},{6},'{7}','{8}','{9}','{10}'",
                                tt.SoBN,tt.SoTBan,tt.SoHS,tt.KetLuan,tt.IDBacSiTH
                                ,tt.IDKTV,tt.LoaiBenhPham,tt.Host,tt.UserName,tt.CDate, tt.pDate);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);

            return ok;
        }

        public static Boolean InsertKetQuaPAP_dtl(string SoBN, int IDBenh, int IDNhom)
        {
            bool ok = false;

            string sp = "usp_GPB_InsertKQPAP_dtl ";
            sp += String.Format("'{0}',{1},{2}",
                               SoBN, IDBenh, IDNhom);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);

            return ok;
        }

        public static Boolean InsertCDThem(string SoTieuBan, string IDHM, int Loai,
                                string MauBenhPham, string ChanDoanGPB, string PUser,string Host
                                , int IDBs, string TenBS)
        {
            bool ok = false;

            string sp = "usp_GPB_InsertCDThem ";
            sp += String.Format("'{0}','{1}',{2},'{3}',N'{4}'"
                                +",N'{5}','{6}',{7},N'{8}'",
                               SoTieuBan, IDHM, Loai, MauBenhPham, ChanDoanGPB
                               , PUser, Host, IDBs, TenBS);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);

            return ok;
        }

        public static Boolean InsertCDHoanTien(string SoTieuBan, string IDHM, string MauBenhPham
                                        , string ChanDoanGPB,int Tien , int IDBs, string PUser
                                        , string Host, int soluong)
        {
            bool ok = false;

            string sp = "usp_GPB_InsertCDHoanTien ";
            sp += String.Format("'{0}','{1}',N'{2}',N'{3}',{4}"
                                +",{5},'{6}','{7}',{8}",
                               SoTieuBan, IDHM, MauBenhPham, ChanDoanGPB,Tien
                               , IDBs, PUser, Host, soluong);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);

            return ok;
        }

        public static Boolean UpdateKetQua(TTKetQuaDTO tt)
        {
            bool ok = false;

            string sp = "usp_GPB_UpdateKQ ";
            sp += String.Format(" '{0}',N{1},N{2},N{3}"
                                + ",'{4}',{5},'{6}','{7}'"
                                +",'{8}','{9}',{10}"
                                +",{11},{12},'{13}','{14}','{15}',{16},{17}"
                                +",{18},'{19}'",
                 tt.SoBN, Uit.it_SQL.ToSQLString(tt.KQDaiThe2), Uit.it_SQL.ToSQLString(tt.KQViThe2)
                , Uit.it_SQL.ToSQLString(tt.KetLuan2)
                , tt.UrlHinh2, tt.IDBacSiTH, tt.CDate,tt.SoTBan
                , tt.Host,tt.UserName,tt.Lan
                , tt.SoLam, tt.IDNguoiCat, tt.NgayCat, tt.NgayLenLam, tt.IDPPNhuom,tt.IDKTV,tt.SoLamHM
                , tt.SoMauCat,tt.GhiChu);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);

            return ok;
        }

        public static Boolean UpdateKetQuaPAP(TTKetQuaPAPDTO tt)
        {
            bool ok = false;

            string sp = "usp_GPB_UpdateKQPAP ";
            sp += String.Format("'{0}',N'{1}',{2},{3},{4}"
                               +" ,'{5}','{6}','{7}'"
                                ,tt.SoBN,tt.KetLuan,tt.IDBacSiTH,tt.IDKTV,tt.LoaiBenhPham
                                ,tt.Host,tt.UserName,tt.pDate);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);

            return ok;
        }

        public static Boolean UpdateXacNhan(string SoBN,string stb, int lan)
        {
            bool ok = false;

            string sp = "usp_GPB_UpdateXN ";
            sp += String.Format(" '{0}','{1}',{2}",
                                    SoBN, stb, lan);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);

            return ok;
        }
        public static Boolean UpdateFee(string SoBN, string SoHS, string SoNVien, bool bThucHien 
                                    , int LoaiBN, string DVCode, SqlConnection ConnPK)
        {
            bool ok = false;
            string sql = "";
         
            switch (LoaiBN)
            {
                case 1:
                    //ngoai tru
                    sql = "UPDATE [ThuNgan].[dbo].[Utb_BaFee] \n"
                           + "SET  \n"
                           + "    [BThucHien] = '" + bThucHien + "' \n"
                           + "    ,[NgayThucHien] = GETDATE() \n"
                           + "WHERE  \n"
                           + "	[sobn] = '" + SoBN + "' \n"
                           + "  AND  [dvucode] = '" + DVCode + "'";
                  
                    break;
                case 2:
                    //noi tru
                    sql = "UPDATE [phkham].[dbo].[feent_dtl] \n"
                       + "SET \n"
                       + "    [BThucHien] = '" + bThucHien + "' \n"
                       + "    ,[NgayThucHien] = GETDATE() \n"
                       + " WHERE  \n"
                       + "	 [sohs] = '" + SoHS + "' \n"
                       + "    AND [sonhapvien] = '" + SoNVien + "' \n"
                       + "    AND [soct] = '" + SoBN + "'  ";
                  
                    break;
                case 3:                    
                case 4:
                    //mien giam - chuong trinh
                    sql = "UPDATE [phkham].[dbo].[bafeemg] \n"
                       + "SET \n"
                       + "    [BThucHien] = '" + bThucHien + "' \n"
                       + "    ,[NgayThucHien] = GETDATE() \n"
                       + " WHERE  \n"
                       + "	[sobn] = '" + SoBN + "' \n"
                       + "   AND [dvucode] = '" + DVCode + "'";
                    break;
                case 5:
                    //ksk
                    sql = "UPDATE  \n"
                       + "	[phkham].[dbo].[bafeeksk] \n"
                       + "SET  \n"
                       + "    [BThucHien] = '" + bThucHien + "' \n"
                       + "    ,[NgayThucHien] = GETDATE() \n"
                       + "WHERE  \n"
                       + "	[sobn] = '" + SoBN + "' \n"
                       + "	AND [dvucode] = '" + DVCode + "'";
                    break;
            } 

            ok = Uit.it_SQL.ExecuteNonQuery(sql, ConnPK);

            return ok;
        }
        public static Boolean DeleteKQ(string SoBN, string SoTB)
        {
            bool ok = false;

            string sp = "usp_GPB_DeleteKQ ";
            sp += String.Format(" '{0}','{1}'", SoBN, SoTB);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);
            return ok;
        }

        public static Boolean DeleteGPB_CD_XNBOXUNG(string SoTB )
        {
            bool ok = false;

            string sp = "usp_DeleteGPB_CD_XNBOXUNG ";
            sp += String.Format(" '{0}'", SoTB);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);
            return ok;
        }

        public static Boolean DeleteGPB_CD_HoanTien(string SoTB)
        {
            bool ok = false;

            string sp = "usp_DeleteGPB_CD_HoanTien ";
            sp += String.Format(" '{0}'", SoTB);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);
            return ok;
        }

        public static Boolean DeleteKQPAP(string SoBN)
        {
            bool ok = false;
            string sp = "usp_GPB_DeleteKQPAP ";
            sp += String.Format(" '{0}'", SoBN);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);
            return ok;
        }

        public static Boolean DeleteKQPAP_dtl(string SoBN)
        {
            bool ok = false;

            string sp = "usp_GPB_DeleteKQPAP_dtl ";
            sp += String.Format(" '{0}'", SoBN);
            ok = Uit.it_SQL.ExecuteNonQuery(sp);
            return ok;
        }

        public static DataTable GetKetQua(string SoBN, string SoHS, string SoTB, int lan)
        {
            DataTable dtKQ = new DataTable();

            string sp = "usp_GPB_GetKQ ";
            sp += String.Format(" '{0}','{1}','{2}',{3}", SoBN, SoHS,SoTB,lan);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetKetQua(string SoBN, string SoHS, string SoTB)
        {
            DataTable dtKQ = new DataTable();

            string sp = "usp_GPB_GetKQAll ";
            sp += String.Format(" '{0}','{1}','{2}'", SoBN, SoHS, SoTB);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetKetQua( string SoTB, int lan)
        {
            DataTable dtKQ = new DataTable();

            string sp = "usp_GPB_GetKQ_STB ";
            sp += String.Format(" '{0}',{1}", SoTB,lan);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetKetQua(int lan, string SoHS )
        {
            DataTable dtKQ = new DataTable();

            string sp = "usp_GPB_GetKQ_SHS ";
            sp += String.Format(" '{0}',{1}", SoHS, lan);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetKetQuaPAP(string SoBN, string SoHS)
        {
            DataTable dtKQ = new DataTable();

            string sp = "usp_GPB_GetKQPAP ";
            sp += String.Format(" '{0}','{1}'", SoBN, SoHS);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }
        
        public static DataTable GetKetQuaPAP_STB(string SoTB)
        {
            DataTable dtKQ = new DataTable();

            string sp = "usp_GPB_GetKQPAP_STB ";
            sp += String.Format(" '{0}'", SoTB);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetKetQuaSTB(string SoTB)
        {
            DataTable dtKQ = new DataTable();

            string sp = "usp_GPB_GetKQbySTB ";
            sp += String.Format(" '{0}'",SoTB);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetMax(int loai, int nam, int thang)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetSoMax ";
            sp += String.Format(" '{0}','{1}','{2}'", loai, nam, thang);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }
    }
}
